import pymysql
from pymysql.cursors import DictCursor
from DBUtils.PooledDB import PooledDB


class Mysql(object):
    __pool = None

    def __init__(self, db_config, db_name):
        self._conn = Mysql.__getconn(db_name, db_config)
        self._cursor = self._conn.cursor(DictCursor)

    @staticmethod
    def __getconn(db_name, db_config):
        if Mysql.__pool is None:
            __pool = PooledDB(creator=pymysql,
                              maxconnections=6,
                              mincached=1,
                              maxcached=5,
                              blocking=False,
                              host=db_config["DbHost"],
                              port=db_config["DbPort"],
                              user=db_config["DbUser"],
                              passwd=db_config["DbPasswd"],
                              database=db_name,
                              use_unicode=True,
                              charset='utf8',
                              cursorclass=DictCursor)
            return __pool.connection()

    def get_all(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = None
        return result

    def get_one(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = None
        return result

    def get_many(self, sql, num, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchmany(num)
        else:
            result = None
        return result

    def insert_one(self, sql, value):
        self._cursor.execute(sql, value)
        return self.__get_insert_id()

    def insert_many(self, sql, values):
        count = self._cursor.executemany(sql, values)
        return count

    def __get_insert_id(self):
        self._cursor.execute("SELECT @@IDENTITY AS id")
        result = self._cursor.fetchall()
        return result[0]['id']

    def __query(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        return count

    def update(self, sql, param=None):
        # print(sql, param)
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        return self.__query(sql, param)

    def begin(self):
        self._conn.autocommit(0)

    def end(self, option='commit'):
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()

    def commit(self):
        self._conn.commit()

    def close(self, is_end=1):
        if is_end == 1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()

    def insert_one_return_id(self, sql, params=None):
        """
        执行插入SQL并返回自增ID
        :param sql: INSERT语句
        :param params: 参数列表或字典
        :return: 最后插入行的自增ID
        """
        cursor = None
        try:
            cursor = self._conn.cursor()
            cursor.execute(sql, params or ())
            self._conn.commit()
            return cursor.lastrowid  # 关键：获取自增ID
        except Exception as e:
            self._conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
